
ALTER  procedure sp_change_reasonCodes_proc

@old_code_no INT,
@code_no INT,
@code_name VARCHAR(50),
@code_type VARCHAR(50),

@error_code VARCHAR(100) OUTPUT

AS
DECLARE @count INT
DECLARE @open_po_count INT

BEGIN

	SELECT @count = COUNT(*) from sales_hold_table where hold_reason_code = @old_code_no 
	IF( @count = 0 ) 	
 	BEGIN
		SET @error_code = 'SUCCESS'
 	END
 	ELSE
 	BEGIN
 		SET @error_code = 'Could not Change Reason codes which are already in use for Sales Orders'		
 	END

	SET @count = 0
		
	IF @error_code = 'SUCCESS'
	BEGIN		
		SELECT @count = COUNT(*) FROM PurchaseOrder_Line_Item_Table WHERE ( ( PO_Finance_Hold = @old_code_no ) OR ( PO_Operation_Hold = @old_code_no ) )
		SELECT @open_po_count = COUNT(*) FROM PurchaseOrder_Table WHERE ( ( PO_Finance_Hold = @old_code_no ) OR ( PO_Operation_Hold = @old_code_no ) )

		IF( ( @count = 0 ) AND ( @open_po_count = 0 ) )
		BEGIN
			IF ( @code_no <> '0' and @code_name <> '' and @code_type <> '' )
 			BEGIN
 				UPDATE Hold_Reason_Codes_Table SET Reason_Code_No = @code_no, Reason_Code_Name = @code_name, Reason_Code_Type = @code_type WHERE Reason_Code_No = @old_code_no
 				SET @error_code = 'SUCCESS'
 			END	 		
	 		ELSE
 		        BEGIN
 				DELETE Hold_Reason_Codes_Table WHERE Reason_Code_No = @old_code_no
 				SET @error_code = 'SUCCESS'
 			END
		END
		ELSE
		BEGIN
			SET @error_code = 'Could not Change Reason codes which are already in use for Purchase Orders'
		END
	END
	ELSE
	BEGIN
		
		SELECT @count = COUNT(*) FROM PurchaseOrder_Line_Item_Table WHERE ( ( PO_Finance_Hold = @old_code_no ) OR ( PO_Operation_Hold = @old_code_no ) )
		IF( @count != 0 )		
		BEGIN
			SET @error_code = 'Could not Change Reason codes which are already in use for Sales Orders and Purchase Orders'
		END		
	END

SELECT @error_code
RETURN

END

